﻿Imports System.Collections.ObjectModel
Imports System.Data.OleDb

Public Class QuizModel
    Inherits ModelWithGUID

    Private _title As String
    Private _creator As UserModel
    Private _creator_id As String
    Private _description As String
    Private _questions As List(Of QuestionModel)

    Public ReadOnly Property Title As String
        Get
            Return _title
        End Get
    End Property

    Public ReadOnly Property HasTitle As Boolean
        Get
            Return _title <> ""
        End Get
    End Property

    Public ReadOnly Property CreatorID As String
        Get
            Return _creator_id
        End Get
    End Property

    Public ReadOnly Property Creator As UserModel
        Get
            If HasCreator And IsNothing(_creator) Then
                _creator = New UserModel(CreatorID)
            End If
            Return _creator
        End Get
    End Property

    Public ReadOnly Property HasCreator As Boolean
        Get
            Return _creator_id <> ""
        End Get
    End Property

    Public ReadOnly Property Description As String
        Get
            Return _description
        End Get
    End Property


    Public ReadOnly Property HasDescription As Boolean
        Get
            Return _description <> ""
        End Get
    End Property

    Public ReadOnly Property Questions As ReadOnlyCollection(Of QuestionModel)
        Get
            Return _questions.AsReadOnly()
        End Get
    End Property

    Public ReadOnly Property QuestionCount As Integer
        Get
            Return Questions.Count
        End Get
    End Property

    Public ReadOnly Property DataSet As DataSet
        Get
            Dim dSet As DataSet = New DataSet()
            Dim questionsTable As DataTable = dSet.Tables.Add("Questions")

            Dim questionGUIDCol As DataColumn = New DataColumn("GUID", GetType(String))
            questionGUIDCol.ReadOnly = True
            questionGUIDCol.Unique = True
            questionsTable.Columns.Add(questionGUIDCol)

            Dim questionSequenceCol As DataColumn = New DataColumn("Sequence", GetType(Integer))
            questionSequenceCol.ReadOnly = True
            questionSequenceCol.Unique = True
            questionsTable.Columns.Add(questionSequenceCol)

            Dim typeCol As DataColumn = New DataColumn("Type", GetType(QuestionType))
            typeCol.ReadOnly = True
            questionsTable.Columns.Add(typeCol)

            Dim questionCol As DataColumn = New DataColumn("Question", GetType(String))
            questionCol.ReadOnly = True
            questionsTable.Columns.Add(questionCol)

            questionsTable.PrimaryKey = New DataColumn() {questionGUIDCol}

            Dim answersTable As DataTable = dSet.Tables.Add("MultipleChoiceAnswers")

            Dim answerGUIDCol As DataColumn = New DataColumn("GUID", GetType(String))
            answerGUIDCol.ReadOnly = True
            answerGUIDCol.Unique = True
            answersTable.Columns.Add(answerGUIDCol)

            Dim answerQuestionGUIDCol As DataColumn = New DataColumn("QuestionGUID", GetType(String))
            answerQuestionGUIDCol.ReadOnly = True
            answersTable.Columns.Add(answerQuestionGUIDCol)

            Dim answerSequenceCol As DataColumn = New DataColumn("Sequence", GetType(Integer))
            answerSequenceCol.ReadOnly = True
            answerSequenceCol.Unique = True
            answersTable.Columns.Add(answerSequenceCol)

            Dim answerCol As DataColumn = New DataColumn("Answer", GetType(String))
            answerCol.ReadOnly = True
            answersTable.Columns.Add(answerCol)

            Dim correctCol As DataColumn = New DataColumn("Correct", GetType(Boolean))
            correctCol.ReadOnly = True
            answersTable.Columns.Add(correctCol)

            answersTable.PrimaryKey = New DataColumn() {answerGUIDCol}

            answersTable.ParentRelations.Add(New DataRelation("QuestionMultipleChoiceAnswerRelation", questionGUIDCol, answerQuestionGUIDCol))

            Dim questionRow As DataRow
            Dim answerRow As DataRow

            For Each question As MultipleChoiceQuestionModel In Questions
                questionRow = questionsTable.NewRow()
                questionRow("GUID") = question.GUID
                questionRow("Sequence") = question.Sequence
                questionRow("Type") = question.Type
                questionRow("Question") = question.Question
                questionsTable.Rows.Add(questionRow)

                For Each answer As MultipleChoiceAnswerModel In question.Answers
                    answerRow = answersTable.NewRow()
                    answerRow("GUID") = answer.GUID
                    answerRow("QuestionGUID") = answer.QuestionGUID
                    answerRow("Sequence") = answer.Sequence
                    answerRow("Answer") = answer.Answer
                    answerRow("Correct") = answer.Correct
                    answersTable.Rows.Add(answerRow)
                Next
            Next

            Return dSet
        End Get
    End Property

    Protected Overrides Sub CreateBlank()
        _title = ""
        _creator_id = ""
        _creator = Nothing
        _description = ""
        _questions = New List(Of QuestionModel)()
    End Sub

    Protected Overrides Function Load() As Boolean
        Dim IsInDB As Boolean
        Dim sqlString As String = "SELECT qz.Title, qz.Creator, qz.Description, qs.GUID AS QuestionGUID " & _
                                  "FROM quizzes AS qz LEFT JOIN questions AS qs " & _
                                  "ON qz.GUID = qs.QuizGUID " & _
                                  "WHERE qz.GUID = @GUID " & _
                                  "ORDER BY qs.Sequence"
        Dim oleDbConn As OleDbConnection = db()
        Dim cmd As OleDbCommand = New OleDbCommand(sqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@QuizGUID", GUID)
        Dim dataReader As OleDbDataReader = cmd.ExecuteReader()
        IsInDB = dataReader.HasRows
        If IsInDB Then
            dataReader.Read()
            GetField(_title, dataReader, "Title", "")
            GetField(_creator_id, dataReader, "Creator", "")
            GetField(_description, dataReader, "Description", "")
            _questions = New List(Of QuestionModel)()
            If Not IsDBNull(dataReader("QuestionGUID")) Then
                Do
                    _questions.Add(New MultipleChoiceQuestionModel(dataReader("QuestionGUID")))
                Loop While dataReader.Read()
            End If
        End If
        oleDbConn.Close()
        Return IsInDB
    End Function

    Public Overrides Function Save() As Boolean
        If IsSaved Then
            Return True
        End If
        Dim sqlString As String
        Dim cmd As OleDbCommand
        Dim rowsAffected As Integer
        Dim oleDbConn As OleDbConnection = db()
        If IsInDB Then
            sqlString = "UPDATE quizzes SET Title=@Title, Creator=@Creator, Description=@Description WHERE GUID=@QuizGUID"
        Else
            sqlString = "INSERT INTO quizzes (Title, Creator, Description, GUID) VALUES (@Title, @Creator, @Description, @GUID)"
        End If
        cmd = New OleDbCommand(sqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@Title", If(HasTitle, Title, DBNull.Value))
        cmd.Parameters.AddWithValue("@Creator", If(HasCreator, CreatorID, DBNull.Value))
        cmd.Parameters.AddWithValue("@Description", If(HasDescription, Description, DBNull.Value))
        cmd.Parameters.AddWithValue("@GUID", GUID)
        rowsAffected = cmd.ExecuteNonQuery()
        oleDbConn.Close()
        For Each question As MultipleChoiceQuestionModel In Questions
            question.Save()
        Next
        If rowsAffected = 1 Then
            IsInDB = True
            _isSaved = True
            Return True
        Else
            Return False
        End If
    End Function

    Public Sub New(ByVal newId As String)
        MyBase.New(newId)
    End Sub

End Class
